package rkdwogus7.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class J20_BookDBDAO implements J20_BookDBService {
	// JDBC 접속 정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	// SQL문 정의 : 미리 테이블 만들 때 SQL문 까지 만들어 봐.
	// 테이블의 이름과 일치시켜 두도록 해.
	private static final String insertSQL = " insert into BookDB(id, title, publisher, author) values(id.nextval, ?, ?, ?) ";
	private static final String selectSQL = " select id, title from BookDB order by id desc ";
	private static final String selectOneSQL = " select * from bookdb where id = ? ";
	private static final String updateSQL = " update BookDB set title = ?, publisher = ?, author = ?, regdate = sysdate where id = ? ";
	private static final String deleteSQL = " delete from bookdb where id = ? ";
	
	@Override
	public int insertBookDB(J20_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublicher());
				ps.setString(3, bvo.getAuthor());
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return cnt;
	}

	@Override
	public String selectBookDB() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = ""; // 나중에 ArrayList로 변경예정
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				for(int i = 1; i <= 2; ++i) {
					result += rs.getString(i) + " ";
				}
				result += "\n";
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) {
				try { rs.close(); } catch (Exception e) { }
			}
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		return result;
	}
	
	@Override
	public String selectBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectOneSQL);
				ps.setString(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				for(int i = 1; i <= 5; ++i) {
					result += rs.getString(i) + " ";
				}
				result += "\n";
			}
				
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) {
				try { rs.close(); } catch (Exception e) { }
			}
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		
		return result;
	}



	@Override
	public int updateBookDB(J20_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublicher());
				ps.setString(3, bvo.getAuthor());
				ps.setString(4, bvo.getId());
			cnt = ps.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		return cnt;
	}

	@Override
	public int deleteBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, id);
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return cnt;
	}
	
	
}
